package com.grab.dao;

import com.grab.domain.SystemArea;
import com.grab.utils.ReflectionUtils;
import com.nxin.etpojar.common.area.AliMapDsUtil;
import com.nxin.etpojar.common.area.AreaResDsBean;
import com.nxin.etpojar.common.pinyin.PinYinDsUtil;
import com.nxin.etpojar.common.string.StringOperationDsUtil;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author YanShuang
 * @veresultSetion v_1.0.1
 * @since 2020/9/14 10:13
 */
public class c_gd_py {

    private static final String GAODE_KEY = "";

    public static void main(String[] args) {
        try {

            //1、注册JDBC驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、获取数据库连接
            Connection connection = DriverManager.getConnection(JdbcConstant.DB_URL, JdbcConstant.USER, JdbcConstant.PASS);
            //3、操作数据库
            Statement statement = connection.createStatement();//获取操作数据库的对象

            ResultSet resultSet = statement.executeQuery("select * from system_area where longitude is null or longitude = '' or area_name_spell = '' or area_name_spell is null");//执行sql，获取结果集
            List<SystemArea> list = new ArrayList<>();
            while(resultSet.next()){
                Map<String, Object> mapMetaData = new HashMap<String, Object>();
                ResultSetMetaData metaData = resultSet.getMetaData();
                //获取数据表中满足要求的一行数据，并放入Map中
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    Object columnValue = resultSet.getObject(columnLabel);
                    // System.out.println(columnLabel);
                    mapMetaData.put(columnLabel, columnValue);
                }
                //将Map中的数据通过反射初始化T类型对象
                if (mapMetaData.size() > 0) {
                    SystemArea obj = SystemArea.class.newInstance();
                    for (Map.Entry<String, Object> entry : mapMetaData.entrySet()) {
                        String fieldkey = entry.getKey();
                        fieldkey = StringOperationDsUtil.underlineToHump(fieldkey);
                        Object fieldvalue = entry.getValue();
                        // System.out.println(fieldkey + ":" + fieldvalue);
                        ReflectionUtils.setFieldValue(obj, fieldkey, fieldvalue);
                        //通过反射赋值
                    }
                    list.add(obj);
                }
            }
            boolean r = true;
            System.out.println(list.size());
            for (int i = 0; i < list.size(); i++) {
                SystemArea systemArea = list.get(i);
                Integer areaId = systemArea.getAreaId();
                String areaFullName = systemArea.getAreaFullName();
                String areaName = systemArea.getAreaName();
                String fullFirstUpper = PinYinDsUtil.getFullFirstUpper(areaName);
                String firstSpellLower = PinYinDsUtil.getFirstSpellLower(areaName).toUpperCase();
                String longitude = systemArea.getLongitude();
                String latitude = systemArea.getLatitude();
                String sql = "update system_area set" +
                        " area_name_spell = '" + firstSpellLower + "'" +
                        ",area_name_acronym = '" + fullFirstUpper + "'";
                if (StringUtils.isBlank(longitude) && StringUtils.isNotBlank(GAODE_KEY)){
                    List<AreaResDsBean> areaToLngAndlat = AliMapDsUtil.getAreaToLngAndlat(areaFullName, GAODE_KEY);
                    if (areaToLngAndlat != null){
                        AreaResDsBean areaResDsBean = areaToLngAndlat.get(0);
                        longitude = areaResDsBean.getLongitude();
                        latitude = areaResDsBean.getLatitude();
                        sql +=  ",longitude = '" + longitude + "'" +
                                ",latitude = '" + latitude + "'";
                    }
                }
                sql += " where area_id = " + areaId;
                System.out.println(sql);
                //执行sql，获取结果集
                int j = statement.executeUpdate(sql);
                if (j != 1) {
                    r = false;
                }
            }
            if (r) {
                System.out.println(" 更新成功 ");
            } else {
                System.out.println(" 更新失败 ");
            }
            //4、关闭结果集、数据库操作对象、数据库连接
            statement.close();
            connection.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
    }
}
